package com.bear.cmcc.NetTestHTTP.db;

import com.bear.cmcc.NetTestHTTP.NetworkTestApp;
import com.bear.cmcc.NetTestHTTP.constant.FtpConstant;
import com.bear.cmcc.NetTestHTTP.constant.MyCommonConstant;
import com.bear.cmcc.NetTestHTTP.service.LocalUpdateVillageDBService;
import com.bear.cmcc.NetTestHTTP.util.CSVReader;
import com.bear.cmcc.NetTestHTTP.util.SetUtil;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import java.io.File;
import java.io.FileReader;
import java.io.IOException;

/**
 * 创建存放小区信息的本地数据库
 * @author meizhen.xu
 *
 */
public class DBCellsHelper extends SQLiteOpenHelper {
    private static final int VERSION = 32; //数据库版本
	
	private final String TAG = "DBCellsHelper";
	
	private static final int CELLS_VERSION = 2;
	public static final String CELLS_DBNAME = "cells.db";
	public static final String TABLES_CEllS_NAME = "t_cells";
	
	private static final String TCELLS_province		= "province";
	private static final String TCELLS_city			= "city";
	private static final String TCELLS_district		= "district";
	private static final String TCELLS_lac			= "lac";
	private static final String TCELLS_cid			= "cid";
	private static final String TCELLS_cellName		= "cellName";
	private static final String TCELLS_longitude	= "longitude";
	private static final String TCELLS_latitude		= "latitude";
	private static final String TCELLS_type			= "type";

	private Context context;

	public DBCellsHelper(Context context, String name, CursorFactory factory,
			int version) {
		super(context, name, factory, version);
		this.context = context;
	}
	public DBCellsHelper(Context context){
		super(context,CELLS_DBNAME,null,CELLS_VERSION);
		this.context = context;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("CREATE TABLE " + TABLES_CEllS_NAME + " (" + TCELLS_province+" TEXT," + TCELLS_city+" TEXT," + TCELLS_district+" TEXT,"
				+TCELLS_lac+ " INTEGER," + TCELLS_cid+" INTEGER," + TCELLS_cellName+" TEXT," + TCELLS_longitude+" real," + TCELLS_latitude+" real,"
				+TCELLS_type+" TEXT" + ");");
		Log.d(TAG, "onCreate");
	}

	@Override
	public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
		// TODO Auto-generated method stub

	}
	
	/**
	 * 小区信息拷入数据库
	 * @param villageService
	 */
	public void insertVillageDB(LocalUpdateVillageDBService villageService){
		villageService.sendFileDownBroad(MyCommonConstant.PrivateActionName.SERVICE_DOWN_VILLAGE_TYPE_DB, 0, 0);
		File file = new File(context.getFilesDir() + "/" + FtpConstant.FtpFileNames.dbFileName);
		FileReader fr = null;
		CSVReader reader = null;
		SQLiteDatabase db = this.getWritableDatabase();	
		try {
			long starttime = System.currentTimeMillis();
			if (!file.exists()){
				villageService.sendFileDownBroad(MyCommonConstant.PrivateActionName.SERVICE_DOWN_VILLAGE_TYPE_DB, -1, 0);
				return ;
			}
			db.execSQL("delete from "+DBCellsHelper.TABLES_CEllS_NAME);
			
			fr = new FileReader(file);
			reader = new CSVReader(fr);
			String[] nextLine = reader.readNext();
			int currentPro = 0;//当前进度
			long totalSize = 108353;//reader.readAll().size();//总的数量
			int current_index = 0;
			int temp_index = 0;
			db.beginTransaction();
		
			while ((nextLine = reader.readNext()) != null) {
				try {
					if(NetworkTestApp.isStop_celldb){
						//取消更新
						return ;
					}
					
					String sql = "insert into " + DBCellsHelper.TABLES_CEllS_NAME
							+ " ("+TCELLS_province+","+TCELLS_city+","+TCELLS_district+","+TCELLS_lac+","+TCELLS_cid+","+TCELLS_cellName+","+TCELLS_longitude+","+TCELLS_latitude+","+TCELLS_longitude+") values ('"
							+ nextLine[0] + "','" + nextLine[1] + "','" + nextLine[2] + "','" + nextLine[3] + "','"
							+ nextLine[4] + "','" + nextLine[5] + "','" + nextLine[6] + "','" + nextLine[7] + "','"
							+ nextLine[8] + "')";
					current_index++;
					db.execSQL(sql);
					
					currentPro = (int) (current_index * 100 / totalSize);
    			    //为了防止频繁的通知导致应用吃紧，百分比增加2才通知一次 
    			    if(temp_index==0 ||current_index-10>temp_index){
    			    	temp_index=current_index;
                		if(currentPro>100){
                			currentPro=100;
                		}
                		villageService.sendFileDownBroad(MyCommonConstant.PrivateActionName.SERVICE_DOWN_VILLAGE_TYPE_DB, 0, currentPro,nextLine[5]);
                	}
				} catch (Exception e) {
					continue;
				}
			}
			long endtime = System.currentTimeMillis();
			Log.d(TAG, "reloadDB-insertTime:" + (endtime - starttime) / 1000);
			villageService.sendFileDownBroad(MyCommonConstant.PrivateActionName.SERVICE_DOWN_VILLAGE_TYPE_DB, 1, 100);
		} catch (Exception e) {
			e.printStackTrace();
			Log.d(TAG, "insertVillageDB-Exception:"+e.getMessage());
			villageService.sendFileDownBroad(MyCommonConstant.PrivateActionName.SERVICE_DOWN_VILLAGE_TYPE_DB, -1, 0);
		} finally {
			try {
				db.setTransactionSuccessful();
				db.endTransaction();
				db.close();
				reader.close();
				fr.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * 查询cellName
	 * @param lac 城市区码
	 * @param ci
	 * @param netType 网络模式
	 * @return cellName
	 */
	public String queryCellName(int lac, int ci, String netType) {
		Cursor cursor=null;
		SQLiteDatabase db=null;
		try
		{
		    db=this.getReadableDatabase();
			cursor = querySQL(db,"select * from "+TABLES_CEllS_NAME +" where "+TCELLS_lac+"=? and "+TCELLS_cid
				+"=? and "+TCELLS_type+"=?", new String[]{String.valueOf(lac),String.valueOf(ci),netType});
			if(cursor==null){
				return null;
			}
			String tellName = null;
			if(cursor.moveToNext()){
				tellName = cursor.getString(cursor.getColumnIndex(TCELLS_cellName));
			}
			return tellName;
		}catch(Exception e)
		{
			return "";
		}finally
		{
			if(cursor!=null)
			{
				try {
                    cursor.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
			}
			
			if(db!=null){
			    try {
                    db.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
			}
		}
		
	}
	
	/**
	 * 执行SQL更新语句
	 * @param sql
	 */
	public void exeSql(String sql){
		try{
			SQLiteDatabase db = this.getWritableDatabase();	
			db.execSQL(sql);
			db.close();
		}catch(SQLiteException e){
			
		}
	}
	/**
	 * 查询语句 
	 * @param sql select * from Table where column1=? and column2=? 
	 * @param args 列条件的值String[]{"value1","value2",....}
	 * @return
	 */
	public Cursor querySQL(SQLiteDatabase db,String sql,String[] args){
		try{
			
			Cursor cursor=db.rawQuery(sql, args);
			return cursor;
		}catch(SQLiteException e){
			return null;
		}
	}
	/**
	 * 插入数据
	 * @param table 数据表名
	 * @param values 数据的链表值
	 */
	public void insert(String table,ContentValues values){
		try{
			SQLiteDatabase db=this.getWritableDatabase();
			db.insert(table, null, values);
			db.close();
			Log.d("myAndroid","This is insert");
		}catch(SQLiteException e){
		}
	}
	
	/**
	 * 查询表数据
	 * @param table 数据表名称
	 * @param columns 提取数据的列String[]{"column1","column2",....}
	 * @return
	 */
	public Cursor queryTable(String table,String[] columns){
		try{
			SQLiteDatabase db=this.getReadableDatabase();
			Cursor cursor=db.query(table, columns, null, null, null, null, null);
			return cursor;
		}catch(SQLiteException e){
			return null;
		}
	}
	/**
	 * 查询表数据
	 * @param table 数据表名称
	 * @param columns 提取数据的列 String[]{"column1","column2",....}
	 * @param groupBy 排序的字段
	 * @param orderBy 排序的类型(asc,desc)
	 * @return
	 */
	public Cursor queryTable(String table,String[] columns,String groupBy,String orderBy){
		try{
			SQLiteDatabase db=this.getReadableDatabase();
			Cursor cursor=db.query(table, columns, null, null, groupBy, null, orderBy);
			return cursor;
		}catch(SQLiteException e){
			return null;
		}
	}
	/**
	 * 查询表数据
	 * @param table 数据表名称
	 * @param columns 提取数据的列 String[]{"column1","column2",....}
	 * @param selection 提取数据的查询条件 column1=? and column2=?
	 * @param selectionArgs 对应查询条件的值 String[]{"value1","value2"}
	 * @return
	 */
	public Cursor queryTable(String table,String[] columns,String selection,String[]selectionArgs){
		try{
			SQLiteDatabase db=this.getReadableDatabase();
			Cursor cursor=db.query(table,columns, selection,selectionArgs, null, null, null);
			return cursor;
		}catch(SQLiteException e){
			return null;
		}
	}
	/**
	 * 查询表数据
	 * @param table 数据表名
	 * @param columns 提取数据的列String[]{"column1","column2",...}
	 * @param selection 提取数据的查询条件 column1=? and column2=?
	 * @param selectionArgs 对应查询条件的值 String[]{"value1","value2"}
	 * @param groupBy 排序的字段
	 * @param orderBy 排序的类型(asc,desc)   
	 * @return
	 */
	public Cursor queryTable(String table,String[] columns,String selection,String[]selectionArgs,String groupBy,String orderBy){
		try{
			SQLiteDatabase db=this.getReadableDatabase();
			Cursor cursor=db.query(table, columns, selection, selectionArgs, groupBy, null, orderBy);
			return cursor;
		}catch(SQLiteException e){
			return null;
		}
	}

}
